﻿using System;
using System.Data;
using System.Data.DBLib;
using System.Web;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    /// <summary>
    /// 对应于表dbf_user, 系统用户
    ///  
    /// </summary>
    /// 
    public class UsersDB : DBBase
    {
        /// <summary>
        /// 登录较验
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public DataRow checkLogin(string strLoginName, string strPassword)
        {
            if (ConnectDB())
            {
                CMFEncode objEncode = new CMFEncode(true, strLoginName);
                string strSql = "select u.*, GroupName, GroupType";
                strSql += " from DBF_USER u, DBF_USER_GROUP g";
                strSql += " where u.GroupId = g.Rowno and LoginName = '";
                strSql += strLoginName.Trim();
                strSql += "' and Password = '";
                strSql += objEncode.encode(strPassword.Trim());
                strSql += "' and IsSuspend = 'N' and IsDelete = 0";
                
                DataRow dr = m_objDB.getDataRow(strSql, "DBF_USER");

                if (dr != null)
                {
                    CDBError objError = new CDBError();
                    strSql = "update DBF_USER set LastActivityDate = getDate() where Rowno = " + GetValue(dr, "Rowno");
                    m_objDB.updateData(strSql, objError);
                }
                else
                {
                    m_strMessage = m_objDB.getLastError();
                }

                return dr;
            }

            return null;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="rowno"></param>
        /// <param name="loginName"></param>
        /// <param name="userName"></param>
        /// <param name="email"></param>
        /// <param name="phoneNum"></param>
        /// <param name="address"></param>
        /// <returns></returns>
        public DataTable GetInfo(string strLoginName, string strUserName, string strGroupName, string strSuspend, int nUserRole)
        {
            /*
                select u.Rowno as UserId, u.LoginName, u.PassWord, 
                u.UserName, u.Email, u.PhoneNum, u.Address, u.Remark, u.LastActivityDate,
                u.CreateIp, u.CreateBy, u.CreateDate, u.UpdateIp, u.UpdateBy, u.UpdateDate,
                g.Rowno as GroupId, g.GroupName, g.GroupType as UserType
                from DBF_USER u, DBF_USER_GROUP g
            */
            if (ConnectDB())
            {
                string strSql = "select u.*, GroupName, dbo.fx_get_item(UserRole, 0, 'en') as UserRoleName from DBF_USER u, DBF_USER_GROUP g where u.GroupId = g.Rowno ";
                
                if (!IsEmpty(strLoginName))
                {
                    strSql += " and LoginName like '%" + strLoginName + "%' ";
                }

                if (!IsEmpty(strUserName))
                {
                    strSql += " and UserName like '%" + strUserName + "%' ";
                }

                if (!IsEmpty(strGroupName))
                {
                    strSql += " and GroupName like '%" + strGroupName + "%' ";
                }

                if (!IsEmpty(strSuspend) && strSuspend != "*")
                {
                    strSql += " and IsSuspend = '" + strSuspend + "' ";
                }

                if (nUserRole > 0)
                {
                    strSql += " and UserRole = " + nUserRole;
                }

                strSql += " and IsDelete = 0";
                return m_objDB.getDataTable(strSql, "DBF_USER");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }

        public DataTable GetResponsiblePerson(string strUserGroupID)
        {
            if (ConnectDB())
            {
                string strSql = "select UserName, LoginName from DBF_USER where GroupID not in (1, 2)";

                if (!IsEmpty(strUserGroupID))
                {
                    strSql += " and GroupID = ";
                    strSql += strUserGroupID;
                }

                strSql += " Union Select '', ''";

                return m_objDB.getDataTable(strSql, "DBF_USER");
            }

            return null;
        }

        public DataRow GetItem(string strUserId)
        {
            if (ConnectDB())
            {
                string strSql = "select u.*, GroupName from DBF_USER u, DBF_USER_GROUP g where u.GroupId = g.Rowno and u.Rowno =  " + strUserId;
                return m_objDB.getDataRow(strSql, "DBF_USER");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }

        public DataRow GetItemByID(string strUserId)
        {
            if (ConnectDB())
            {
                string strSql = "select * from DBF_USER where LoginName =  '" + strUserId + "'";
                return m_objDB.getDataRow(strSql, "DBF_USER");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }

        public int Insert(string strGroupId, string strLoginName, string strPasswd, string strUserName, string strEmail, string strPhoneNum, string strAddress, string strRemark, string strSuspend, int nUserRole, int nAdvanceAlertDay)
        {
            if (ConnectDB())
            {
                DataRow dr = GetItemByID(strLoginName);

                if (dr == null)
                {
                    CMFEncode objEncode = new CMFEncode(true, strLoginName);
                    CDBError objError = new CDBError();
                    string strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
                    string strUserIp = HttpContext.Current.Request.UserHostAddress;
                    string strSql = "insert into DBF_USER(GroupId, LoginName, PassWord, UserName, Email, PhoneNum, Address, Remark, IsSuspend, UserRole , AdvanceAlertDay";
                    strSql += ", CreateIp, CreateBy, CreateDate, UpdateIp, UpdateBy, UpdateDate)";
                    strSql += " values(";
                    strSql += strGroupId;
                    strSql += ", '";
                    strSql += strLoginName;
                    strSql += "', '";
                    strSql += objEncode.encode(strPasswd);
                    strSql += "', '";
                    strSql += strUserName;
                    strSql += "', '";
                    strSql += strEmail;
                    strSql += "', '";
                    strSql += strPhoneNum;
                    strSql += "', '";
                    strSql += strAddress;
                    strSql += "', '";
                    strSql += strRemark;
                    strSql += "', '";
                    strSql += strSuspend;
                    strSql += "', ";
                    strSql += nUserRole;
                    strSql += ",";
                    strSql += nAdvanceAlertDay;
                    strSql += ", '";
                    strSql += strUserIp;
                    strSql += "', '";
                    strSql += strUserId;
                    strSql += "', getDate(), '";
                    strSql += strUserIp;
                    strSql += "', '";
                    strSql += strUserId;
                    strSql += "', getDate())";
                    m_objDB.updateData(strSql, objError);
                    m_strMessage = objError.Message;
                    return objError.RetCode;
                }
                else
                {
                    m_strMessage = "Login Name Already Exists!";
                }
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Update(string strRowno, string strGroupId, string strUserName, string strEmail, string strPhoneNum, string strAddress, string strRemark, string strSuspend, int nUserRole, int nAdvanceAlertDay)
        {
            if (IsEmpty(strRowno))
            {
                m_strMessage = "Record Not Found!";
            }
            else
            {
                if (ConnectDB())
                {
                    CDBError objError = new CDBError();
                    string strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
                    string strUserIp = HttpContext.Current.Request.UserHostAddress;
                    string strSql = "update DBF_USER set GroupId = " + strGroupId;
                    strSql += ", UserName = '" + strUserName;
                    strSql += "', Email = '" + strEmail;
                    strSql += "', PhoneNum = '" + strPhoneNum;
                    strSql += "', Address = '" + strAddress;
                    strSql += "', Remark = '" + strRemark;
                    strSql += "', IsSuspend = '" + strSuspend;
                    strSql += "', UserRole = " + nUserRole;
                    strSql += " , AdvanceAlertDay = " + nAdvanceAlertDay;
                    strSql += " , UpdateBy = '" + strUserId;
                    strSql += "', UpdateIp = '" + strUserIp;
                    strSql += "', UpdateDate = getDate()";
                    strSql += " where Rowno = " + strRowno;
                    m_objDB.updateData(strSql, objError);
                    m_strMessage = objError.Message;
                    return objError.RetCode;
                }
                else
                {
                    m_strMessage = "Cannot Connect to the Database!";
                }
            }

            return -1;
        }

        public int Delete(string strRowno)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strUserId = HttpContext.Current.Session[SysUtil.m_str_session_username].ToString();
                string strUserIp = HttpContext.Current.Request.UserHostAddress;
                string strSql = "update DBF_USER set IsDelete = 1, DeleteBy = '";
                strSql += strUserId;
                strSql += "', DeleteIP = '";
                strSql += strUserIp;
                strSql += "', DeleteDate = getdate() where Rowno = " + strRowno;
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Trash(string strRowno)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "delete from DBF_USER where Rowno = " + strRowno;
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int ChangePassword(string strLoginName, string strOldPassword, string strNewPassword)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                DataRow dr = GetItemByID(strLoginName);

                if (dr != null)
                {
                    string strPassword = m_objDB.GetStringValue(dr, "Password");
                    CMFEncode objEncode = new CMFEncode(true, strLoginName);

                    if (strPassword == objEncode.encode(strOldPassword))
                    {
                        string strSql = "update DBF_USER set Password = '";
                        strSql += objEncode.encode(strNewPassword);
                        strSql += "' where LoginName = '";
                        strSql += strLoginName;
                        strSql += "'";
                        m_objDB.updateData(strSql, objError);
                        m_strMessage = objError.Message;
                        return objError.RetCode;
                    }
                    else
                    {
                        m_strMessage = "Invalid Old Password!";
                    }
                }
                else
                {
                    m_strMessage = "No Record Found!";
                }
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int ResetPassword(string strLoginName, string strNewPassword)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                DataRow dr = GetItemByID(strLoginName);

                if (dr != null)
                {
                    CMFEncode objEncode = new CMFEncode(true, strLoginName);
                    string strSql = "update DBF_USER set Password = '";
                    strSql += objEncode.encode(strNewPassword);
                    strSql += "' where LoginName = '";
                    strSql += strLoginName;
                    strSql += "'";
                    m_objDB.updateData(strSql, objError);
                    m_strMessage = objError.Message;
                    return objError.RetCode;
                }
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }
    }
}
